package com.aisino.api.service;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.aisino.api.config.EntityCacheConfig;
import com.aisino.api.enums.EntityYDLXEnum;
import com.aisino.api.model.*;
import com.aisino.api.model.SubStoreInfoRequest;
import com.aisino.api.util.*;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.http.HttpEntity;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

import java.util.Map;
import java.util.stream.Collectors;

@Service
public class DrugStandardService {

    private static final Logger logger = LoggerFactory.getLogger(DrugStandardService.class);
    private static final String NOT_DELETE = "00";
    private static final String DELETED = "01";
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate dmJdbcTemplate;

    @Value("${ygyd.uploadfile.serverpath}")
    private String uploadFileServerPath;

    /**
     * 获取药品列表数据
     *
     * @param request 请求参数
     * @return 药品标准库数据响应
     */
    public DrugStandardResponse getDrugList(DrugStandardRequest request) {
        DrugStandardResponse response = new DrugStandardResponse();

        logger.info("Starting to fetch drug list data with reqId: {}", request.getReqId());

        // 设置默认值
        int size = (request.getSize() != null) ? request.getSize() : 100;
        if (size > 500) {
            size = 500; // 限制最大返回条数为500
        }

        logger.info("Query size limit set to: {}", size);

        // 构建查询SQL
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT YPWYBSM, YPTYMC, YPYWMC, YPSPMC, YPBWM, YPCF, YYJJ, TSRQ, GNZZ, YFYL, YPXZ, ");
        sql.append("BZGG, BLFS, CCTJ, YPYXQ, YPYXQDW, ZYSX, XHZY, YPPZWH, YPPZWHYXQ, JX, ZJGG, ");
        sql.append("YPSSXKCYRMC, YPSCQYMC, YPZCFL, TSYPGLFL, CFYBS, YBMLYP, ZDYPBS, GCHJKYP, ");
        sql.append(" UPDATE_TIME, YPFL,YPTXM,YPBSM,YPPYSZM  ");
        sql.append("FROM YGYD_MAIN.DRUG_STANDARDS ");
        sql.append("WHERE ROWNUM <= ? ");

        // 如果有更新时间参数，则添加时间过滤条件
        if (request.getUpdateTime() != null && !request.getUpdateTime().isEmpty()) {
            sql.append("AND UPDATE_TIME > TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS.FF3') ");
        }
        sql.append("AND DELETE_FLAG = ? AND YPZT=1 ");
        sql.append("ORDER BY UPDATE_TIME");

        logger.info("Executing SQL query: {}", sql.toString());

        // 查询数据
        List<Map<String, Object>> dataList;
        if (request.getUpdateTime() != null && !request.getUpdateTime().isEmpty()) {
            logger.info("Query parameters: size={}, updateTime={}", size, request.getUpdateTime(), NOT_DELETE);
            dataList = dmJdbcTemplate.queryForList(sql.toString(), size, request.getUpdateTime(), NOT_DELETE);
        } else {
            logger.info("Query parameters: size={}", size);
            dataList = dmJdbcTemplate.queryForList(sql.toString(), size, NOT_DELETE);
        }

        logger.info("Query returned {} records", dataList.size());

        // 转换数据格式
        List<DrugStandardResponse.DrugStandardData> resultDataList = new ArrayList<>();
        for (Map<String, Object> row : dataList) {
            DrugStandardResponse.DrugStandardData data = new DrugStandardResponse.DrugStandardData();
            data.setYpwybsm((String) row.get("YPWYBSM"));
            data.setYptymc((String) row.get("YPTYMC"));
            data.setYppzwh((String) row.get("YPPZWH"));
            data.setYpssxkcyrmc((String) row.get("YPSSXKCYRMC"));
            data.setYpscqymc((String) row.get("YPSCQYMC"));
            data.setYpzcfl(getStringValue(row.get("YPZCFL")));
            data.setTsypglfl(getStringValue(row.get("TSYPGLFL")));
            data.setCfybs(getStringValue(row.get("CFYBS")));
            data.setYbmlyp(getStringValue(row.get("YBMLYP")));
            data.setGchjkyp(getStringValue(row.get("GCHJKYP")));
            data.setYpfl((String) row.get("YPFL"));
            data.setYpyxq(getIntegerValue(row.get("YPYXQ")));
            data.setYpyxqdw(getStringValue(row.get("YPYXQDW")));
            data.setBzgg((String) row.get("BZGG"));
            data.setZjgg((String) row.get("ZJGG"));
            data.setJx((String) row.get("JX"));
            data.setYyjj((String) row.get("YYJJ"));
            data.setTsrq((String) row.get("TSRQ"));
            data.setGnzz((String) row.get("GNZZ"));
            data.setYfyl((String) row.get("YFYL"));
            data.setYpxz((String) row.get("YPXZ"));
            data.setBlfs((String) row.get("BLFS"));
            data.setCctj((String) row.get("CCTJ"));
            data.setZysx((String) row.get("ZYSX"));
            data.setXhzy((String) row.get("XHZY"));
            data.setYptxm((String) row.get("YPTXM"));
            data.setYpbsm((String) row.get("YPBSM"));
            data.setYppyszm((String) row.get("YPPYSZM"));
            // 设置更新时间
            Object updateTimeObj = row.get("UPDATE_TIME");
            if (updateTimeObj != null) {
                data.setUpdateTime(updateTimeObj.toString());
            }
            resultDataList.add(data);
        }

        response.setCode(ResultCode.SUCCESS.getCode());
        response.setMsg(ResultCode.SUCCESS.getMessage());
        response.setReqId(request.getReqId());
        response.setData(resultDataList);

        logger.info("Successfully processed drug list data with reqId: {}", request.getReqId());
        return response;
    }

    /**
     * 根据uscc查询企业信息
     *
     * @param uscc 企业统一社会信用代码
     * @return 企业信息Map，包含ID、TYXYDM、QYMC、DQBM等字段
     */
    private Entity queryEntityInfo(String uscc, String appId) {
        //如果是本企业
        Entity entityMe = EntityCacheConfig.ENTITY_CACHE.get(appId);
        if (entityMe != null && (entityMe.getTyxydm().equals(uscc) || "nocheck".equals(uscc))) {
            //如果是本单位，直接返回
            return entityMe;
        }
        String queryEntitySql = "SELECT QYID, TYXYDM, QYMC, DQBM FROM YGYD_MAIN.ENTITY_SUB_STORE WHERE TYXYDM = ? AND DELETE_FLAG = ?";
        logger.info("Executing query entity SQL: {}", queryEntitySql);
        logger.info("Query entity parameters: uscc={}", uscc);
        List<Entity> entityList = dmJdbcTemplate.query(queryEntitySql, (rs, rowNum) -> {
            Entity entity = new Entity();
            entity.setId(rs.getString("QYID"));
            entity.setDqbm(rs.getString("DQBM"));
            entity.setTyxydm(rs.getString("TYXYDM"));
            entity.setQymc(rs.getString("QYMC"));
            return entity;
        }, uscc, NOT_DELETE);
        if (entityList.isEmpty()) {
            throw new RuntimeException("统一社会信用代码错误，请核对，只能传本单位或下属门店的（下属门店信息通过总部账户在接口管理中维护）");
        }
        return entityList.get(0);
    }
    /**
     * 根据uscc查询企业信息
     *
     * @param uscc 企业统一社会信用代码
     * @return 企业信息Map，包含ID、TYXYDM、QYMC、DQBM等字段
     */
    private Entity queryLsmdEntityInfo(String uscc) {
        String queryEntitySql = "SELECT ID, TYXYDM, QYMC, DQBM FROM YGYD_MAIN.ENTITY WHERE TYXYDM = ? AND DELETE_FLAG = ? and QYLX=?";
        logger.info("Executing query entity SQL: {}", queryEntitySql);
        logger.info("Query entity parameters: uscc={}", uscc);
        List<Entity> entityList = dmJdbcTemplate.query(queryEntitySql, (rs, rowNum) -> {
            Entity entity = new Entity();
            entity.setId(rs.getString("ID"));
            entity.setDqbm(rs.getString("DQBM"));
            entity.setTyxydm(rs.getString("TYXYDM"));
            entity.setQymc(rs.getString("QYMC"));
            return entity;
        }, uscc, NOT_DELETE, EntityYDLXEnum.LSMD.getValue());
        if (entityList.isEmpty()) {
            throw new RuntimeException("统一社会信用代码在系统中不存在，请核对，如果是新注册的门店，需等待数据同步后才能新增"+uscc);
        }
        return entityList.get(0);
    }

    /**
     * 上传药品库存信息
     *
     * @param request 请求参数
     * @return 上传结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugStandardResponse uploadDrugInventory(DrugInventoryRequest request) {
        DrugStandardResponse response = new DrugStandardResponse();
        response.setReqId(request.getReqId());
        List<DrugInventoryRequest.DrugInventoryData> dataList = JSONUtil.parseArray(request.getData()).toList(DrugInventoryRequest.DrugInventoryData.class);
        request.setItemList(dataList);
        logger.info("Starting to process drug inventory upload with reqId: {}", request.getReqId());
        // 1. 验证请求参数
        DrugInventoryRequest.ValidationResult validationResult = request.validate();
        if (validationResult != null) {
            response.setCode(validationResult.getCode());
            response.setMsg(validationResult.getMessage());
            return response;
        }

        // 1. 通过uscc查询企业信息（使用公共方法）
        Entity entityInfo = queryEntityInfo(request.getUscc(), request.getAppId());
        String entityId = entityInfo.getId();
        String uscc = entityInfo.getTyxydm();
        String entityName = entityInfo.getQymc();
        String dqbm = entityInfo.getDqbm();
        logger.info("Found entity with ID: {}, USCC: {}, Name: {}, DQBM: {}", entityId, uscc, entityName, dqbm);

        // 2. 批量验证初始化库存只能做一次
        List<String> initInventoryItems = new ArrayList<>();
        Set<String> ypwybsmSet = new HashSet<>();
        for (int i = 0; i < dataList.size(); i++) {
            DrugInventoryRequest.DrugInventoryData item = dataList.get(i);
            // 检查ypwybsm是否在当前批次中重复
            if (!ypwybsmSet.add(item.getYpwybsm())) {
                throw new RuntimeException("药品唯一标识码(ypwybsm)在当前批次中重复: " + item.getYpwybsm());
            }
            if (item.getPdlx() == 1) {
                initInventoryItems.add(item.getYpwybsm());
            }
        }

        if (!initInventoryItems.isEmpty()) {
            String initCheckSql = "SELECT YPWYBSM FROM YGYD_MAIN.DRUG_STORE_INVENTORY_RECORD WHERE QYID = ? AND PDLX = 1 AND YPWYBSM IN (" +
                    initInventoryItems.stream().map(s -> "?").collect(Collectors.joining(",")) + ")";
            Object[] initCheckParams = new Object[1 + initInventoryItems.size()];
            initCheckParams[0] = entityId;
            for (int i = 0; i < initInventoryItems.size(); i++) {
                initCheckParams[i + 1] = initInventoryItems.get(i);
            }

            List<Map<String, Object>> existingInitRecords = dmJdbcTemplate.queryForList(initCheckSql, initCheckParams);
            if (!existingInitRecords.isEmpty()) {
                StringJoiner ypwybsmJoiner = new StringJoiner(",");
                for (Map<String, Object> record : existingInitRecords) {
                    ypwybsmJoiner.add((String) record.get("YPWYBSM"));
                }
                throw new RuntimeException("初始化库存只能做一次（请使用定期盘点），唯一标识码=" + ypwybsmJoiner.toString());
            }
        }

        // 3. 批量处理药品库存数据
        if (dataList != null && !dataList.isEmpty()) {
            // 分离初始化库存和定期盘点数据
            List<DrugInventoryRequest.DrugInventoryData> initItems = new ArrayList<>();
            List<DrugInventoryRequest.DrugInventoryData> regularItems = new ArrayList<>();
            List<String> allYpwybsmList =new ArrayList<>();
            for (DrugInventoryRequest.DrugInventoryData item : dataList) {
                if (item.getPdlx() == 1) {
                    initItems.add(item);
                } else {
                    regularItems.add(item);
                }
                allYpwybsmList.add(item.getYpwybsm());
            }
            // 批量查询所有药品信息（用于确保 YPWYBSM 的准确性） 后续用到
            Map<String, Map<String, Object>> allDrugInfoMap = new HashMap<>();
            if (!allYpwybsmList.isEmpty()) {
                String allDrugInfoQuerySql = "SELECT YPWYBSM, YPTYMC, YPSPMC, YPPZWH, YPBWM, CFYBS, YBMLYP, ZDYPBS, GCHJKYP, TSYPGLFL, BZGG, ZJGG, YPFL, YPZCFL, YPSCQYMC, YPSSXKCYRMC, SFWLJS " +
                        "FROM YGYD_MAIN.DRUG_STANDARDS WHERE YPWYBSM IN (" +
                        allYpwybsmList.stream().map(s -> "?").collect(Collectors.joining(",")) + ") and DELETE_FLAG=?";

                Object[] allDrugInfoQueryParams = new Object[allYpwybsmList.size() + 1];
                for (int i = 0; i < allYpwybsmList.size(); i++) {
                    allDrugInfoQueryParams[i] = allYpwybsmList.get(i);
                }
                allDrugInfoQueryParams[allYpwybsmList.size()] = NOT_DELETE;

                List<Map<String, Object>> allDrugInfos = dmJdbcTemplate.queryForList(allDrugInfoQuerySql, allDrugInfoQueryParams);
                for (Map<String, Object> drugInfo : allDrugInfos) {
                    allDrugInfoMap.put((String) drugInfo.get("YPWYBSM"), drugInfo);
                }
            }
            // 批量查询所有药品信息（用于确保 YPWYBSM 的准确性）

            // 批量更新定期盘点数据
            if (!regularItems.isEmpty()) {
                String regularUpdateSql = "UPDATE YGYD_MAIN.DRUG_STORE SET KCSL = ? WHERE YPWYBSM = ? AND QYID = ?";
                List<Object[]> regularUpdateBatch = new ArrayList<>();

                for (DrugInventoryRequest.DrugInventoryData item : regularItems) {
                    regularUpdateBatch.add(new Object[]{item.getSl(), item.getYpwybsm(), entityId});
                }

                logger.info("Executing batch update drug store for regular items, count: {}", regularUpdateBatch.size());
                int[] regularUpdateResults = dmJdbcTemplate.batchUpdate(regularUpdateSql, regularUpdateBatch);
                logger.info("Batch updated {} regular items in DRUG_STORE", regularUpdateResults.length);
            }

            // 查找未更新的药品（需要插入新记录）
            List<DrugInventoryRequest.DrugInventoryData> itemsToInsert = new ArrayList<>();

            if (!allYpwybsmList.isEmpty()) {
                String selectExistingSql = "SELECT YPWYBSM FROM YGYD_MAIN.DRUG_STORE WHERE QYID = ? AND YPWYBSM IN (" +
                        allYpwybsmList.stream().map(s -> "?").collect(Collectors.joining(",")) + ")";
                Object[] selectExistingParams = new Object[1 + allYpwybsmList.size()];
                selectExistingParams[0] = entityId;
                for (int i = 0; i < allYpwybsmList.size(); i++) {
                    selectExistingParams[i + 1] = allYpwybsmList.get(i);
                }

                List<Map<String, Object>> existingRecords = dmJdbcTemplate.queryForList(selectExistingSql, selectExistingParams);
                Set<String> existingYpwybsmSet = existingRecords.stream()
                        .map(record -> (String) record.get("YPWYBSM"))
                        .collect(Collectors.toSet());

                for (DrugInventoryRequest.DrugInventoryData item : dataList) {
                    if (!existingYpwybsmSet.contains(item.getYpwybsm())) {
                        itemsToInsert.add(item);
                    }
                }
            }

            // 批量插入新的药品库存记录
            if (!itemsToInsert.isEmpty()) {
                String insertDrugStoreSql = "INSERT INTO YGYD_MAIN.DRUG_STORE " +
                        "(ID, QYID, DQBM, YPWYBSM, QYMC, TYXYDM, KCSL, KCSLTJ, DATA_STATUS, IS_ON_SALE, CREATE_USER, CREATE_TIME, UPDATE_USER, UPDATE_TIME, DELETE_FLAG, ZBQYID) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP, ?, ?)";

                List<Object[]> insertDrugStoreBatch = new ArrayList<>();
                for (DrugInventoryRequest.DrugInventoryData item : itemsToInsert) {
                    Map<String, Object> drugInfo = allDrugInfoMap.get(item.getYpwybsm());
                    if (drugInfo == null) {
                        throw new RuntimeException("未找到对应的药品信息，请检查ypwybsm和yppzwh是否正确ypwybsm=" + item.getYpwybsm() + "yppzwh=" + item.getYppzwh());
                    }

                    String id = IdWorker.getIdStr();
                    Object[] insertParams = {
                            id,
                            entityId,
                            dqbm,
                            drugInfo.get("YPWYBSM"),
                            entityName,
                            uscc,
                            item.getSl(), // 库存数量
                            item.getSl(), // 库存数量
                            1,
                            1,
                            "system",
                            "system",
                            NOT_DELETE,
                            request.getAppId()
                    };
                    insertDrugStoreBatch.add(insertParams);
                }

                logger.info("Executing batch insert into DRUG_STORE with {} records", insertDrugStoreBatch.size());
                dmJdbcTemplate.batchUpdate(insertDrugStoreSql, insertDrugStoreBatch);
                logger.info("Successfully inserted {} new records into DRUG_STORE", insertDrugStoreBatch.size());
            }

            // 批量插入库存盘点记录到DRUG_STORE_INVENTORY_RECORD表
            String insertInventoryRecordSql = "INSERT INTO YGYD_MAIN.DRUG_STORE_INVENTORY_RECORD " +
                    "(ID, QYID, DQBM, YPWYBSM, SJLY, PDLX, KCSL, CREATE_USER, CREATE_TIME, PDSJ) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";

            List<Object[]> insertInventoryRecordBatch = new ArrayList<>();



            for (DrugInventoryRequest.DrugInventoryData item : dataList) {
                Map<String, Object> drugInfo = allDrugInfoMap.get(item.getYpwybsm());
                if (drugInfo == null) {
                    throw new RuntimeException("未找到对应的药品信息，请检查ypwybsm和yppzwh是否正确ypwybsm=" + item.getYpwybsm() + "yppzwh=" + item.getYppzwh());
                }

                String recordId = IdWorker.getIdStr();
                Object[] recordParams = {
                        recordId,
                        entityId,
                        dqbm,
                        drugInfo.get("YPWYBSM"),
                        1,
                        item.getPdlx(),
                        item.getSl(), // 库存数量
                        "system"
                };
                insertInventoryRecordBatch.add(recordParams);
            }

            logger.info("Executing batch insert into DRUG_STORE_INVENTORY_RECORD with {} records", insertInventoryRecordBatch.size());
            dmJdbcTemplate.batchUpdate(insertInventoryRecordSql, insertInventoryRecordBatch);
            logger.info("Successfully inserted {} records into DRUG_STORE_INVENTORY_RECORD", insertInventoryRecordBatch.size());
        }

        response.setCode(ResultCode.SUCCESS.getCode());
        response.setMsg(ResultCode.SUCCESS.getMessage());

        logger.info("Successfully processed drug inventory upload with reqId: {}", request.getReqId());
        return response;
    }


    /**
     * 从DRUG_STANDARDS表查询药品信息
     *
     * @param ypwybsm 药品唯一标识码
     * @param yppzwh  药品批准文号
     * @return 药品信息Map
     */
    private Map<String, Object> queryDrugStandardInfo(String ypwybsm, String yppzwh) {
        String queryDrugSql = "SELECT YPWYBSM, YPTYMC, YPSPMC, YPPZWH, YPBWM, CFYBS, YBMLYP, ZDYPBS, GCHJKYP, TSYPGLFL, BZGG, ZJGG, YPFL, YPZCFL, YPSCQYMC, YPSSXKCYRMC, SFWLJS " +
                "FROM YGYD_MAIN.DRUG_STANDARDS WHERE YPWYBSM = ? and DELETE_FLAG=?";// and YPPZWH = ?
        logger.info("Executing query drug standard SQL: {}", queryDrugSql);
        logger.info("Query drug standard parameters: ypwybsm={}, yppzwh={}", ypwybsm,NOT_DELETE, yppzwh);
        List<Map<String, Object>> drugList = dmJdbcTemplate.queryForList(queryDrugSql, ypwybsm,NOT_DELETE/*, yppzwh*/);
        if (drugList.isEmpty()) {
            throw new RuntimeException("未找到对应的药品信息，请检查ypwybsm和yppzwh是否正确ypwybsm="+ypwybsm+"yppzwh="+yppzwh);
        }
        return drugList.get(0);
    }

    /**
     * 从 DRUG_STORE 表查询本单位的药品信息
     *
     * @param ypwybsm 药品唯一标识码
     * @param yppzwh  药品批准文号
     * @return 药品信息Map
     */
    private Map<String, Object> queryMyDrugStandardInfo(String ypwybsm, String yppzwh,String entityId) {
        String queryDrugSql = "SELECT a.YPWYBSM, a.YPTYMC, a.YPSPMC, a.YPPZWH, a.YPBWM, a.CFYBS, a.YBMLYP, a.ZDYPBS, a.GCHJKYP, a.TSYPGLFL, " +
                "a.BZGG, a.ZJGG, a.YPFL, a.YPZCFL, a.YPSCQYMC, a.YPSSXKCYRMC, a.SFWLJS " +
                "FROM YGYD_MAIN.DRUG_STANDARDS a,YGYD_MAIN.DRUG_STORE b WHERE a.YPWYBSM=b.YPWYBSM and a.YPWYBSM = ? and b.QYID=?";// and YPPZWH = ?
        logger.info("Executing query drug standard SQL: {}", queryDrugSql);
        logger.info("Query drug standard parameters: ypwybsm={}, yppzwh={}", ypwybsm, yppzwh);
        List<Map<String, Object>> drugList = dmJdbcTemplate.queryForList(queryDrugSql, ypwybsm,entityId);
        if (drugList.isEmpty()) {
            return null;
        }
        return drugList.get(0);
    }

    /**
     * 上传药品入库信息
     *
     * @param request 请求参数
     * @return 上传结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugStandardResponse uploadDrugIn(DrugInRequest request) {
        DrugStandardResponse response = new DrugStandardResponse();
        response.setReqId(request.getReqId());
        List<DrugInRequest.DrugInData> dataList = JSONUtil.parseArray(request.getData()).toList(DrugInRequest.DrugInData.class);
        request.setItemList(dataList);
        logger.info("Starting to process drug in upload with reqId: {}", request.getReqId());

        // 1. 验证请求参数
        DrugInRequest.ValidationResult validationResult = request.validate();
        if (validationResult != null) {
            response.setCode(validationResult.getCode());
            response.setMsg(validationResult.getMessage());
            return response;
        }

        // 2. 通过uscc查询企业信息（使用公共方法）
        Entity entityInfo = queryEntityInfo(request.getUscc(), request.getAppId());
        String entityId = entityInfo.getId();
        String uscc = entityInfo.getTyxydm();
        String entityName = entityInfo.getQymc();
        String dqbm = entityInfo.getDqbm();
        logger.info("Found entity with ID: {}, USCC: {}, Name: {}, DQBM: {}", entityId, uscc, entityName, dqbm);

        // 3. 验证data集合中的每个参数是否合法
        if (dataList != null && !dataList.isEmpty()) {
            // 准备批量插入的数据
            List<Object[]> batchData = new ArrayList<>();
            List<Object[]> zsmBatchData = new ArrayList<>(); // 药品追溯码数据
            List<String> errorYpwybsmList = new ArrayList<>(); // 存储所有错误的ypwybsm

            for (int i = 0; i < dataList.size(); i++) {
                DrugInRequest.DrugInData item = dataList.get(i);
                // 验证每个item的参数
                DrugInRequest.ValidationResult itemValidationResult = item.validate(i + 1);
                if (itemValidationResult != null) {
                    response.setCode(itemValidationResult.getCode());
                    response.setMsg(itemValidationResult.getMessage());
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return response;
                }
                // 从DRUG_STANDARDS表查询药品信息（使用公共方法）
                Map<String, Object> drugInfo = queryMyDrugStandardInfo(item.getYpwybsm(), item.getYppzwh(),entityId);
                if(drugInfo==null){
                    // 收集错误的ypwybsm和错误信息
                    errorYpwybsmList.add(item.getYpwybsm());
                    continue;
                }
                if(errorYpwybsmList.size()==0) {
                    // 生成主键
                    String id = IdWorker.getIdStr();

                    // 构建批量插入数据
                    Object[] data = {
                            id,
                            entityId,
                            dqbm,
                            "1", // 数据来源：1-ERP系统
                            item.getCrklx(),
                            item.getCrksj(),
                            item.getCrkck(),
                            item.getJbr(),
                            item.getYpwybsm(),
                            item.getYppzwh(),
                            item.getScrq(),
                            item.getCpph(),
                            item.getYxqz(),
                            item.getSl(),
                            item.getDj() != null ? item.getDj() : 0.0,
                            item.getGysmc(),
                            item.getGystyxydm(),
                            //item.getYpzsm(),
                            item.getBz(),
                            "system",
                            "system",
                            Convert.toStr(drugInfo.get("YPTYMC")),
                            Convert.toStr(drugInfo.get("YPSPMC")),
                            Convert.toStr(drugInfo.get("YPBWM")),
                            Convert.toStr(drugInfo.get("CFYBS")),
                            Convert.toStr(drugInfo.get("YBMLYP")),
                            Convert.toStr(drugInfo.get("ZDYPBS")),
                            Convert.toStr(drugInfo.get("GCHJKYP")),
                            Convert.toStr(drugInfo.get("TSYPGLFL")),
                            Convert.toStr(drugInfo.get("BZGG")),
                            Convert.toStr(drugInfo.get("YPZCFL")),
                            Convert.toStr(drugInfo.get("YPFL")),
                            Convert.toStr(drugInfo.get("ZJGG")),
                            Convert.toStr(drugInfo.get("SFWLJS")),
                            2,//直接已确认
                            IdWorker.getId(),
                            uscc,
                            entityName,
                            request.getAppId()//总部id
                    };
                    batchData.add(data);
                    logger.info("Adding drug in data: {}", data);
                    // 处理药品追溯码
                    if (StringUtils.hasText(item.getYpzsm())) {
                        String[] ypzsmArray = item.getYpzsm().split(",");
                        for (String ypzsm : ypzsmArray) {
                            if (ypzsm != null && !ypzsm.trim().isEmpty()) {
                                Object[] zsmData = {
                                        IdWorker.getIdStr(),
                                        id,
                                        ypzsm.trim()
                                };
                                zsmBatchData.add(zsmData);
                            }
                        }
                    }
                }
            }
            // 如果有错误的ypwybsm，设置回滚并返回错误信息
            if (!errorYpwybsmList.isEmpty()) {
                response.setCode(ResultCode.NOCHUSHIHUA.getCode());
                response.setMsg("操作失败：存在未进行库存初始化的商品，详细的唯一标识码已通过data参数返回" );
                response.setData(String.join(",", errorYpwybsmList));
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return response;
            }
            // 批量插入DRUG_CRK_TZ_IN表
            String insertDrugCrkInSql = "INSERT INTO YGYD_BIZ.DRUG_CRK_TZ_IN " +
                    "(ID, QYID, DQBM, SJLY, CRKLX, CRKSJ, CRKCK, JBR, YPWYBSM, YPPZWH, SCRQ, CPPH, YXQZ, YPPZS, DJ, GYSMC, GYSTYXYDM, BZ, CREATE_USER, CREATE_TIME, UPDATE_USER, UPDATE_TIME, YPTYMC, YPSPMC, YPBWM, CFYBS, YBMLYP, ZDYPBS, GCHJKYP,TSYPGLFL, BZGG, YPZCFL, YPFL, ZJGG, SFWLJS, ZT, PX, TYXYDM, QYMC,ZBQYID) " +
                    "VALUES (?, ?, ?, ?, ?, TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, ?, ?, TO_DATE(?, 'YYYY-MM-DD'), ?, TO_DATE(?, 'YYYY-MM-DD'), ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            logger.info("Executing batch insert into DRUG_CRK_TZ_IN with {} records", batchData.size());
            dmJdbcTemplate.batchUpdate(insertDrugCrkInSql, batchData);
            logger.info("Successfully inserted {} records into DRUG_CRK_TZ_IN", batchData.size());

            // 批量插入DRUG_CRK_TZ_IN_ZSM表
            if (!zsmBatchData.isEmpty()) {
                String insertDrugCrkInZsmSql = "INSERT INTO YGYD_BIZ.DRUG_CRK_TZ_IN_ZSM " +
                        "(ID, RKID, YPZSM) VALUES (?, ?, ?)";

                logger.info("Executing batch insert into DRUG_CRK_TZ_IN_ZSM with {} records", zsmBatchData.size());
                dmJdbcTemplate.batchUpdate(insertDrugCrkInZsmSql, zsmBatchData);
                logger.info("Successfully inserted {} records into DRUG_CRK_TZ_IN_ZSM", zsmBatchData.size());
            }
        }

        response.setCode(ResultCode.SUCCESS.getCode());
        response.setMsg(ResultCode.SUCCESS.getMessage());

        logger.info("Successfully processed drug in upload with reqId: {}", request.getReqId());
        return response;
    }

    /**
     * 上传药品出库信息
     *
     * @param request 请求参数
     * @return 上传结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugStandardResponse uploadDrugOut(DrugOutRequest request) {
        DrugStandardResponse response = new DrugStandardResponse();
        response.setReqId(request.getReqId());
        List<DrugOutRequest.DrugOutData> dataList = JSONUtil.parseArray(request.getData()).toList(DrugOutRequest.DrugOutData.class);
        request.setItemList(dataList);
        logger.info("Starting to process drug out upload with reqId: {}", request.getReqId());

        // 1. 验证请求参数
        DrugOutRequest.ValidationResult validationResult = request.validate();
        if (validationResult != null) {
            response.setCode(validationResult.getCode());
            response.setMsg(validationResult.getMessage());
            return response;
        }

        // 2. 通过uscc查询企业信息（使用公共方法）
        Entity entityInfo = queryEntityInfo(request.getUscc(), request.getAppId());
        String entityId = entityInfo.getId();
        String uscc = entityInfo.getTyxydm();
        String entityName = entityInfo.getQymc();
        String dqbm = entityInfo.getDqbm();
        logger.info("Found entity with ID: {}, USCC: {}, Name: {}, DQBM: {}", entityId, uscc, entityName, dqbm);

        // 3. 验证data集合中的每个参数是否合法
        if (dataList != null && !dataList.isEmpty()) {
            // 准备批量插入的数据
            List<Object[]> batchData = new ArrayList<>();
            List<Object[]> zsmBatchData = new ArrayList<>(); // 药品追溯码数据
            List<String> errorYpwybsmList = new ArrayList<>(); // 存储所有错误的ypwybsm

            for (int i = 0; i < dataList.size(); i++) {
                DrugOutRequest.DrugOutData item = dataList.get(i);

                // 验证每个item的参数
                DrugOutRequest.ValidationResult itemValidationResult = item.validate(i + 1);
                if (itemValidationResult != null) {
                    response.setCode(itemValidationResult.getCode());
                    response.setMsg(itemValidationResult.getMessage());
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return response;
                }

                Map<String, Object> drugInfo = queryMyDrugStandardInfo(item.getYpwybsm(), item.getYppzwh(),entityId);
                if(drugInfo==null){
                    // 收集错误的ypwybsm和错误信息
                    errorYpwybsmList.add(item.getYpwybsm());
                    continue;
                }
                if(errorYpwybsmList.size()==0) {
                    // 生成主键
                    String id = IdWorker.getIdStr();

                    // 构建批量插入数据
                    Object[] data = {
                            id,
                            entityId,
                            dqbm,
                            "1", // 数据来源：1-ERP系统
                            item.getCrklx(),
                            item.getCrksj(),
                            item.getJbr(),
                            item.getYpwybsm(),
                            item.getYppzwh(),
                            item.getScrq(),
                            item.getCpph(),
                            item.getYxqz(),
                            item.getSl(),
                            item.getDj() != null ? item.getDj() : 0.0,
                            item.getXsdj() != null ? item.getXsdj() : 0.0,
                            //item.getYpzsm(),
                            item.getGmryzjlx(),
                            item.getGmryzjhm(),
                            item.getGmryxm(),
                            item.getXsqd(),
                            item.getXsxspt(),
                            item.getBz(),
                            "system",
                            "system",
                            Convert.toStr(drugInfo.get("YPTYMC")),
                            Convert.toStr(drugInfo.get("YPSPMC")),
                            Convert.toStr(drugInfo.get("YPBWM")),
                            Convert.toStr(drugInfo.get("CFYBS")),
                            Convert.toStr(drugInfo.get("YBMLYP")),
                            Convert.toStr(drugInfo.get("ZDYPBS")),
                            Convert.toStr(drugInfo.get("GCHJKYP")),
                            Convert.toStr(drugInfo.get("TSYPGLFL")),
                            Convert.toStr(drugInfo.get("BZGG")),
                            Convert.toStr(drugInfo.get("YPZCFL")),
                            Convert.toStr(drugInfo.get("YPFL")),
                            Convert.toStr(drugInfo.get("ZJGG")),
                            Convert.toStr(drugInfo.get("SFWLJS")),
                            2,//已确认
                            IdWorker.getId(),
                            uscc,
                            entityName,
                            request.getAppId()
                    };
                    batchData.add(data);

                    // 处理药品追溯码
                    if (StringUtils.hasText(item.getYpzsm())) {
                        String[] ypzsmArray = item.getYpzsm().split(",");
                        for (String ypzsm : ypzsmArray) {
                            if (ypzsm != null && !ypzsm.trim().isEmpty()) {
                                Object[] zsmData = {
                                        IdWorker.getIdStr(),
                                        id,
                                        ypzsm.trim()
                                };
                                zsmBatchData.add(zsmData);
                            }
                        }
                    }
                }
            }
            // 如果有错误的ypwybsm，设置回滚并返回错误信息
            if (!errorYpwybsmList.isEmpty()) {
                response.setCode(ResultCode.NOCHUSHIHUA.getCode());
                response.setMsg("操作失败：存在未进行库存初始化的商品，详细的唯一标识码已通过data参数返回" );
                response.setData(String.join(",", errorYpwybsmList));
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return response;
            }
            // 批量插入DRUG_CRK_TZ_OUT表
            String insertDrugCrkOutSql = "INSERT INTO YGYD_BIZ.DRUG_CRK_TZ_OUT " +
                    "(ID, QYID, DQBM, SJLY, CRKLX, CRKSJ, JBR, YPWYBSM, YPPZWH, SCRQ, CPPH, YXQZ, YPPZS, DJ, XSDJ, GMRYZJLX, GMRYZJHM, GMRYXM, XSQD, XSXSPT, BZ, CREATE_USER, CREATE_TIME, UPDATE_USER, UPDATE_TIME, YPTYMC, YPSPMC, YPBWM, CFYBS, YBMLYP, ZDYPBS, GCHJKYP,TSYPGLFL, BZGG, YPZCFL, YPFL, ZJGG, SFWLJS, ZT, PX, TYXYDM, QYMC,ZBQYID) " +
                    "VALUES (?, ?, ?, ?, ?, TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, ?, TO_DATE(?, 'YYYY-MM-DD'), ?, TO_DATE(?, 'YYYY-MM-DD'), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";

            logger.info("Executing batch insert into DRUG_CRK_TZ_OUT with {} records", batchData.size());
            dmJdbcTemplate.batchUpdate(insertDrugCrkOutSql, batchData);
            logger.info("Successfully inserted {} records into DRUG_CRK_TZ_OUT", batchData.size());

            // 批量插入DRUG_CRK_TZ_OUT_ZSM表
            if (!zsmBatchData.isEmpty()) {
                String insertDrugCrkOutZsmSql = "INSERT INTO YGYD_BIZ.DRUG_CRK_TZ_OUT_ZSM " +
                        "(ID, CKID, YPZSM) VALUES (?, ?, ?)";

                logger.info("Executing batch insert into DRUG_CRK_TZ_OUT_ZSM with {} records", zsmBatchData.size());
                //dmJdbcTemplate.batchUpdate(insertDrugCrkOutZsmSql, zsmBatchData);
                try {
                    dmJdbcTemplate.batchUpdate(insertDrugCrkOutZsmSql, new BatchPreparedStatementSetter() {
                        @Override
                        public void setValues(PreparedStatement ps, int i) throws SQLException {
                            Object[] params = zsmBatchData.get(i);
                            for (int j = 0; j < params.length; j++) {
                                try {
                                    ps.setObject(j + 1, params[j]);
                                } catch (SQLException e) {
                                    // 在这里可以捕获到设置参数时的异常，比如字段超长
                                    logger.error("设置第{}条数据的第{}个参数时出错，参数值: {}", i + 1, j + 1, params[j], e);
                                    throw new SQLException("第" + (i + 1) + "条数据第" + (j + 1) + "个参数设置失败，值为: " + params[j], e);
                                }
                            }
                        }

                        @Override
                        public int getBatchSize() {
                            return zsmBatchData.size();
                        }
                    });
                } catch (DataAccessException e) {
                    logger.error("批处理执行失败，SQL: {}", insertDrugCrkOutZsmSql, e);
                    throw new RuntimeException("批处理执行失败：" + e.getMessage(), e);
                }
                logger.info("Successfully inserted {} records into DRUG_CRK_TZ_OUT_ZSM", zsmBatchData.size());
            }
        }

        response.setCode(ResultCode.SUCCESS.getCode());
        response.setMsg(ResultCode.SUCCESS.getMessage());

        logger.info("Successfully processed drug out upload with reqId: {}", request.getReqId());
        return response;
    }

    /**
     * 上传温湿度信息
     *
     * @param request 请求参数
     * @return 上传结果
     */
    
    /**
     * 维护下属门店信息
     * @param request 请求参数
     * @return 处理结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugStandardResponse editSubStore(Map<String, String> request) {
        DrugStandardResponse response = new DrugStandardResponse();

        // 获取请求参数
        String appId = request.get("appId");
        String reqId = request.get("reqId");
        String type = request.get("type");
        String data = request.get("data");

        // 设置响应reqId
        response.setReqId(reqId);

        if (type == null || type.isEmpty()) {
            response.setCode(ResultCode.PARAM_ERROR.getCode());
            response.setMsg("type不能为空");
            return response;
        }

        if (data == null || data.isEmpty()) {
            response.setCode(ResultCode.PARAM_ERROR.getCode());
            response.setMsg("data不能为空");
            return response;
        }

        // 验证type参数是否合法
        if (!"1".equals(type) && !"2".equals(type)) {
            response.setCode(ResultCode.PARAM_ERROR.getCode());
            response.setMsg("type参数不合法，1表示新增，2表示删除");
            return response;
        }

        // 获取当前企业信息
        Entity entityMe = EntityCacheConfig.ENTITY_CACHE.get(appId);
        if (entityMe == null) {
            response.setCode(ResultCode.PARAM_ERROR.getCode());
            response.setMsg("无效的appId");
            return response;
        }

        // 分割data参数获取统一社会信用代码列表
        String[] tyxydmList = data.split(",");

        if ("1".equals(type)) {
            List<Object[]> params= new ArrayList<>();
            List<String> idList= new ArrayList<>();
            StringBuilder huyue = new StringBuilder();
            // 新增操作
            StringJoiner insertTyxydm = new StringJoiner(",");
            for (String tyxydm : tyxydmList) {
                if(!StringUtils.isEmpty(tyxydm) && !entityMe.getTyxydm().equals(tyxydm)) {
                    // 验证执照号在ENTITY表中是否存在
                    Entity entity = queryLsmdEntityInfo(tyxydm.trim());

                    // 检查是否已存在,已存在抛出异常
                    String checkSql = "SELECT COUNT(1) FROM YGYD_MAIN.ENTITY_SUB_STORE WHERE PID = ? AND TYXYDM = ? AND DELETE_FLAG = ?";
                    int existCount = dmJdbcTemplate.queryForObject(checkSql, Integer.class, entityMe.getId(), tyxydm, NOT_DELETE);

                    if (existCount == 0) {
                        insertTyxydm.add(tyxydm);
                        String id = IdWorker.getIdStr();
                        logger.info("Inserted sub store with tyxydm: {} for entity: {}", tyxydm, entityMe.getId());
                        idList.add(entity.getId());
                        params.add(new Object[]{id, entityMe.getId(), entity.getId(), entity.getTyxydm(), entity.getQymc(), entity.getDqbm()
                                , "system", "system", NOT_DELETE});
                    }else{
                        huyue.append(tyxydm+"已存在，");
                    }
                }else {
                    huyue.append("不能添加本企业为下属门店:"+tyxydm);
                }
            }
            if(params.size()>0){
                // 插入数据到ENTITY_SUB_STORE表
                String insertSql = "INSERT INTO YGYD_MAIN.ENTITY_SUB_STORE " +
                        "(ID, PID, QYID, TYXYDM,QYMC,DQBM, CREATE_USER, CREATE_TIME, UPDATE_USER, UPDATE_TIME, DELETE_FLAG) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP,?)";
                dmJdbcTemplate.batchUpdate(insertSql,params);
                // 更新ENTITY表是否开通接口字段
                NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dmJdbcTemplate);
                String updateSql = "UPDATE YGYD_MAIN.ENTITY SET SFKTJK = :sfktjk WHERE ID in (:ids)";
                MapSqlParameterSource map = new MapSqlParameterSource();
                map.addValue("sfktjk", 1);
                map.addValue("ids", idList);
                namedParameterJdbcTemplate.update(updateSql, map);
                response.setData(insertTyxydm.toString());
                response.setMsg("成功插入"+params.size()+"数据 "+huyue);
            }else{
                response.setMsg("无新增数据 "+huyue);
            }
        } else if ("2".equals(type)) {
            // 构造IN查询的占位符
            StringBuilder placeholders = new StringBuilder();
            List<Object> params = new ArrayList<>();
            List<Object> ps = new ArrayList<>();
            params.add(DELETED); // DELETE_FLAG参数
            params.add(entityMe.getId()); // PID参数
            params.add(NOT_DELETE); // DELETE_FLAG参数
            ps.add("0");

            for (int i = 0; i < tyxydmList.length; i++) {
                if (i > 0) {
                    placeholders.append(",");
                }
                placeholders.append("?");
                params.add(tyxydmList[i].trim());
                ps.add(tyxydmList[i].trim());
            }

            String deleteSql = "UPDATE YGYD_MAIN.ENTITY_SUB_STORE SET DELETE_FLAG = ?, UPDATE_TIME = CURRENT_TIMESTAMP " +
                    "WHERE PID = ? AND DELETE_FLAG = ? AND TYXYDM IN (" + placeholders.toString() + ")";

            String deleteEntitySql = "UPDATE YGYD_MAIN.ENTITY SET SFKTJK = ? WHERE TYXYDM IN (" + placeholders.toString() + ")";

            // 执行批量更新
            dmJdbcTemplate.update(deleteEntitySql, ps.toArray());

            // 执行批量更新
            int updatedRows = dmJdbcTemplate.update(deleteSql, params.toArray());
            logger.info("Deleted {} sub store records for entity: {}", updatedRows, entityMe.getId());
            response.setMsg("成功删除" + updatedRows + "条记录");
        }

        response.setCode(ResultCode.SUCCESS.getCode());

        return response;
    }
    
    /**
     * 上传温湿度信息
     *
     * @param request 请求参数
     * @return 上传结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugStandardResponse uploadHumitureInfo(HumitureInfoRequest request) {
        DrugStandardResponse response = new DrugStandardResponse();
        response.setReqId(request.getReqId());
        List<HumitureInfoRequest.HumitureData> dataList = JSONUtil.parseArray(request.getData()).toList(HumitureInfoRequest.HumitureData.class);
        request.setItemList(dataList);
        logger.info("Starting to process humiture info upload with reqId: {}", request.getReqId());
        // 1. 验证请求参数
        HumitureInfoRequest.ValidationResult validationResult = request.validate();
        if (validationResult != null) {
            response.setCode(validationResult.getCode());
            response.setMsg(validationResult.getMessage());
            return response;
        }
        // 1. 通过uscc查询企业信息（使用公共方法）
        Entity entityInfo = queryEntityInfo(request.getUscc(), request.getAppId());

        String entityId = entityInfo.getId();
        String uscc = entityInfo.getTyxydm();
        String entityName = entityInfo.getQymc();
        String dqbm = entityInfo.getDqbm();

        // 3. 验证data集合中的每个参数是否合法
        if (dataList != null && !dataList.isEmpty()) {
            // 准备批量插入的数据
            List<Object[]> batchData = new ArrayList<>();

            for (HumitureInfoRequest.HumitureData item : dataList) {
                // 确定温湿度状态
                Boolean[] inRange = HumitureUtil.checkTemperatureAndHumidityInRange(item.getSbazqy(),
                        item.getWd(), item.getSd());
                int zt = 1;
                if (!inRange[0] && !inRange[1]) {
                    zt = 4;
                } else if (!inRange[0] && inRange[1]) {
                    zt = 2;
                } else if (inRange[0] && !inRange[1]) {
                    zt = 3;
                }

                // 生成主键
                String id = IdWorker.getIdStr();

                // 构建批量插入数据
                Object[] data = {
                        id,
                        entityId,
                        dqbm,
                        item.getWd(),
                        item.getSd(),
                        item.getSbazqy(),
                        zt,
                        item.getCjsj(),
                        "system",
                };
                batchData.add(data);
            }

            // 批量插入ENTITY_HUMITURE_INFO表
            String insertHumitureSql = "INSERT INTO YGYD_MAIN.ENTITY_HUMITURE_INFO " +
                    "(ID, QYID, DQBM, WD, SD, SBAZQY, ZT, CJSJ, CREATE_USER, CREATE_TIME, UPDATE_TIME) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS'), ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";

            logger.info("Executing batch insert into ENTITY_HUMITURE_INFO with {} records", batchData.size());
            dmJdbcTemplate.batchUpdate(insertHumitureSql, batchData);
            logger.info("Successfully inserted {} records into ENTITY_HUMITURE_INFO", batchData.size());
        }

        response.setCode(ResultCode.SUCCESS.getCode());
        response.setMsg(ResultCode.SUCCESS.getMessage());

        logger.info("Successfully processed humiture info upload with reqId: {}", request.getReqId());
        return response;
    }

    /**
     * 门店基本信息完善
     *
     * @param request 请求参数
     * @return 处理结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugStandardResponse editSubStoreInfo(SubStoreInfoRequest request) {
        DrugStandardResponse response = new DrugStandardResponse();
        response.setReqId(request.getReqId());

        // 1. 验证请求参数
        SubStoreInfoRequest.ValidationResult validationResult = request.validate();
        if (validationResult != null) {
            response.setCode(validationResult.getCode());
            response.setMsg(validationResult.getMessage());
            return response;
        }

        // 2. 通过uscc查询企业信息（使用公共方法）
        Entity entityInfo = queryEntityInfo(request.getUscc(), request.getAppId());
        if(entityInfo == null){
            response.setCode(-2);
            response.setMsg("uscc参数不正确");
            return response;
        }
        String entityId = entityInfo.getId();
        String uscc = entityInfo.getTyxydm();
        String entityName = entityInfo.getQymc();
        String dqbm = entityInfo.getDqbm();

        // 3. 解析营业时间范围
        String[] timeRange = request.getYysj().split("~");
        String yykssj = timeRange[0].trim(); // 营业开始时间
        String yyjssj = timeRange[1].trim(); // 营业结束时间

        // 4. 更新门店信息
        String updateSql = "UPDATE YGYD_MAIN.ENTITY SET " +
                "SFYB = ?, " +                 // 是否医保定点药店
                "SFESSXS = ?, " +              // 是否24小时营业（根据营业时间判断）
                "YYKSSJ = ?, " +               // 营业开始时间
                "YYJSSJ = ?, " +               // 营业结束时间
                "YDDH = ?, " +                 // 药店联系电话
                "LONGITUDE = ?, " +            // 经度
                "LATITUDE = ?, " +             // 纬度
                "QYTP = ?, " +                 // 企业图片
                "UPDATE_TIME = CURRENT_TIMESTAMP " +
                "WHERE TYXYDM = ? AND DELETE_FLAG = ?";

        // 判断是否24小时营业
        String sfessxs = "2"; // 默认不是24小时营业
        if ("00:00".equals(yykssj) && "23:59".equals(yyjssj)) {
            sfessxs = "1"; // 24小时营业
        }

        //这里需要增加图片上传功能
        String img = request.getImg();
        img = uploadFile(img, uploadFileServerPath);
        int updatedRows = dmJdbcTemplate.update(updateSql,
                request.getSfyb(),             // 是否医保定点药店
                sfessxs,                       // 是否24小时营业
                yykssj,                        // 营业开始时间
                yyjssj,                        // 营业结束时间
                request.getLxdh(),             // 药店联系电话
                request.getLongitude(),        // 经度
                request.getLatitude(),         // 纬度
                img,              // 企业图片
                uscc,                          // 统一社会信用代码
                NOT_DELETE                     // 删除标志
        );

        if (updatedRows > 0) {
            response.setCode(ResultCode.SUCCESS.getCode());
            response.setMsg(ResultCode.SUCCESS.getMessage());
        } else {
            response.setCode(ResultCode.FAILURE.getCode());
            response.setMsg("更新门店信息失败");
        }


        return response;
    }

    private String uploadFile(String base64Data, String uploadFileServerPath) {
        try {
            if (base64Data == null || base64Data.isEmpty()){
                return null;
            }
            //如何通过base64Data字符串 解析文件类型
            FileInfo fileInfo=FileUtil.getFileInfo(base64Data);
            DevFileBase64UploadParam param = new DevFileBase64UploadParam();
            param.setFileData(base64Data);
            param.setFileType(fileInfo.getMimeType());
            param.setFileName(IdWorker.getIdStr()+"."+fileInfo.getExtension());
            // 构造请求参数
            String requestBody = JSONUtil.toJsonStr(param);

            // 调用文件服务接口上传文件
            // 假设文件服务地址为: http://localhost:8080/dev/file/uploadBase64DynamicReturnUrl

            String body = HttpUtil.post(uploadFileServerPath,requestBody);
            //转换为jsonobject
            JSONObject jsonObject = JSONUtil.parseObj(body);
            int code = jsonObject.getInt("code");
            if(code==200){
                return jsonObject.getStr("data");
            }
        } catch (Exception e) {
            logger.error("调用文件服务上传base64文件失败: ", e);
        }

        return null;
    }

    /**
     * 检查药品是否已存在
     *20251113 日调整算法
     * 1 如果是医保药品, 则使用医保编码(查询DRUG_MEDICARE_DIRECTORY表得出记录)
     * @param drugInfo 药品信息
     * @return 药品唯一标识码，如果不存在则返回null
     */
    private String checkDrugExists(DrugInfoRequest.DrugInfoData drugInfo,DrugUtils drugUtils) {
//        yptxm	字符串	是	药品条形码
//        ypbsm	字符串	是	药品追溯码前7位
//        gjybbm	字符串	是	国家医保编码
//        ypcf	字符串	是	药品成份
//        yyjj	字符串	是	用药禁忌
//        tsrq	字符串	是	特殊人群
//        gnzz	字符串	是	功能主治
//        yfyl	字符串	是	用法用量
//        ypxz	字符串	是	药品性状
//        blfs	字符串	是	不良反应
//        cctj	字符串	是	储藏条件
//        zysx	字符串	是	注意事项
//        xhzy	字符串	是	相互作用
//        yptp	字符串	是	药品图片，多个图片地址用英文逗号隔开（http或https开头的互联网访问地址）
        String ymblyp=drugInfo.getYbmlyp();//医保目录药品
        String gjybbm=drugInfo.getGjybbm();//国家医保编码
        //如果传入了条形码，则直接用条形码匹配
        if (StringUtils.hasText(gjybbm)) {

            List<Map<String, Object>> result = dmJdbcTemplate.queryForList("SELECT YPWYBSM FROM YGYD_MAIN.DRUG_STANDARDS WHERE GJYBBM = ?",gjybbm);
            if (!result.isEmpty()) {
                String ypwybsm = (String) result.get(0).get("YPWYBSM");
                logger.info("Drug already exists with yptxm yppzwh: {} {}", drugInfo.getYptxm(),drugInfo.getYppzwh());
                return ypwybsm;
            }
            //如果传了医保编码,去医保目录表找一条记录
            String sql = "SELECT REALITY_OUTLOOK,REGISTERED_PRODUCT_NAME,REGISTERED_MEDICINE_MODEL,GOODS_CODE,GOODS_NAME,APPROVAL_CODE,COMPANY_NAME_SC,DRUG_VALIDITY_DATE,FACTOR,GOODS_STANDARD_CODE,INDICATION,IS_OTC,NAME_OR_HOLDER,REGISTERED_MEDICINE_MODEL,USAGE_DOSAGE,MIN_UNIT,UNIT FROM YGYD_MAIN.DRUG_MEDICARE_DIRECTORY WHERE GOODS_CODE = ?";
            List<Map<String, Object>> gjyb_result = dmJdbcTemplate.queryForList(sql, gjybbm);
            // 如果医保目录中没有找到相关记录，直接返回null
            if (gjyb_result.isEmpty()) {
                return null;
            }
            //从医保目录导入一条数据到本地标准库
            return drugUtils.insertNewStandardsDrug(gjyb_result.get(0),dmJdbcTemplate,"1");
        }

        // 使用语义比较的方式查找相似药品，但要求批准文号必须一致
        if (!StringUtils.hasText(drugInfo.getYppzwh())) {
            logger.info("Drug approval number (YPPZWH) is empty, skipping semantic matching");
            return null;
        }

        String sql = "SELECT YPWYBSM, YPPZWH, YPTYMC, YPSPMC, ZJGG, BZGG, YPSSXKCYRMC, YPSCQYMC FROM YGYD_MAIN.DRUG_STANDARDS WHERE YPPZWH = ? AND DELETE_FLAG = ?";

        logger.info("Checking drugs for semantic matching with YPPZWH: {}", drugInfo.getYppzwh());

        List<Map<String, Object>> result = dmJdbcTemplate.queryForList(sql, drugInfo.getYppzwh(), NOT_DELETE);

        if (!result.isEmpty()) {
            // 遍历数据库中的药品，寻找语义上最相似的药品
            double highestSimilarity = 0.0;
            String bestMatchYpwybsm = null;

            for (Map<String, Object> row : result) {
                double similarity = calculateDrugSimilarity(drugInfo, row);

                // 如果相似度超过阈值且是目前最高的，则更新最佳匹配
                if (similarity > 0.85 && similarity > highestSimilarity) {
                    highestSimilarity = similarity;
                    bestMatchYpwybsm = (String) row.get("YPWYBSM");
                }
            }

            if (bestMatchYpwybsm != null) {
                logger.info("Drug semantically matched with YPWYBSM: {} (similarity: {})", bestMatchYpwybsm, highestSimilarity);
                return bestMatchYpwybsm;
            }
        }


        String bzgg = drugInfo.getBzgg();
        String zjgg = drugInfo.getZjgg();
        //通过SpecNormalizer提取最小包装特征
        List<Map.Entry<Double, String>> specsAll = SpecNormalizer.normSpec(bzgg);
        List<Map.Entry<Double, String>> specs2 = SpecNormalizer.normSpec(zjgg);
        specsAll.addAll(specs2);


        Map<String, Object> findRow=new HashMap<>();
        //都没有找到,去医保目录表找一条记录
        sql = "SELECT REALITY_OUTLOOK,REGISTERED_PRODUCT_NAME,REGISTERED_MEDICINE_MODEL,GOODS_CODE,GOODS_NAME,APPROVAL_CODE,COMPANY_NAME_SC,DRUG_VALIDITY_DATE,FACTOR,GOODS_STANDARD_CODE,INDICATION,IS_OTC,NAME_OR_HOLDER,REGISTERED_MEDICINE_MODEL,USAGE_DOSAGE,MIN_UNIT,UNIT FROM YGYD_MAIN.DRUG_MEDICARE_DIRECTORY WHERE APPROVAL_CODE = ?";
        List<Map<String, Object>> gjyb_result = dmJdbcTemplate.queryForList(sql, drugInfo.getYppzwh());
        // 如果医保目录中没有找到相关记录，直接返回null
        if (gjyb_result.isEmpty()) {
            return null;
        }
        for(Map<String, Object> row : gjyb_result){
            String goods_code= Convert.toStr(row.get("GOODS_CODE"));
            //先比较gjybbm 是否一致,如果一致,证明相同直接写入一条标准库,然后返回
            if(goods_code.equals(gjybbm)){
                return drugUtils.insertNewStandardsDrug(row,dmJdbcTemplate,"1");
            } else {
                int factor = Convert.toInt(row.get("FACTOR"),0);//最小包装肯定是整数,转换成整数后比较大小
                //通过包装规格模糊匹配
                for (Map.Entry<Double, String> spec : specsAll){
                    int spec_factor =Convert.toInt(spec.getKey());
                    if(spec_factor==factor && spec.getValue().equals(row.get("MIN_UNIT"))){
                        //找到了
                        findRow=row;
                        break;//完全匹配,直接跳出
                    } else if (spec_factor==factor) {
                        findRow=row;
                    }
                }
            }
        }
        //如果没有找到匹配项，默认使用第一个
        if(findRow.isEmpty()){
            findRow=gjyb_result.get(0);//默认使用第一个
        }
        logger.info("Saving drugs for 国家医保目录 with YPPZWH: {}", drugInfo.getYppzwh());
        return drugUtils.insertNewStandardsDrug(findRow,dmJdbcTemplate,"2");
    }

    /**
     * 计算两个药品之间的语义相似度
     *
     * @param drugInfo 用户提供的药品信息
     * @param dbDrug 数据库中的药品记录
     * @return 相似度分数（0-1之间）
     */
    private double calculateDrugSimilarity(DrugInfoRequest.DrugInfoData drugInfo, Map<String, Object> dbDrug) {
        double totalWeight = 0.0;
        double similarityScore = 0.0;

        // 包装规格比较
        String bzgg1 = drugInfo.getBzgg();
        String bzgg2 = (String) dbDrug.get("BZGG");
        if (StringUtils.hasText(bzgg1) && StringUtils.hasText(bzgg2)) {
            double sim = calculateTextSimilarity(bzgg1, bzgg2);
            similarityScore += sim * 1;  // 5%权重
            totalWeight += 1;
        }
        if(similarityScore<0.85) {
            totalWeight = 0.0;
            similarityScore = 0.0;
            List<Map.Entry<Double, String>> res1 = SpecNormalizer.normSpec(bzgg1);
            List<Map.Entry<Double, String>> res2 = SpecNormalizer.normSpec(bzgg2);
            //比较两个结果的相似度
            if (ObjectUtil.isAllNotEmpty(res1,res2)) {
                double sim = new DrugStandardService().calculateTextSimilarity(res1.get(0).getValue(), res2.get(0).getValue());
                similarityScore += sim * 0.5;
                totalWeight += 0.5;
                sim = new DrugStandardService().calculateTextSimilarity(String.valueOf(res1.get(0).getKey()), String.valueOf(res2.get(0).getKey()));
                similarityScore += sim * 0.5;
                totalWeight += 0.5;
            }
        }
        // 如果没有任何可比较的字段，返回0
        if (totalWeight == 0) {
            return 0.0;
        }

        // 返回加权平均相似度
        return similarityScore / totalWeight;
    }

    public static void main(String[] args) {
        double totalWeight = 0.0;
        double similarityScore = 0.0;
        String bzgg1 = "0.4ml*10支";
        String bzgg2 = "1.0*10支";
        List<Map.Entry<Double, String>> res1 = SpecNormalizer.normSpec(bzgg1);
        List<Map.Entry<Double, String>> res2 = SpecNormalizer.normSpec(bzgg2);
        //比较两个结果的相似度
        if (ObjectUtil.isAllNotEmpty(res1,res2)) {
            double sim = new DrugStandardService().calculateTextSimilarity(res1.get(0).getValue(), res2.get(0).getValue());
            similarityScore += sim * 0.5;
            totalWeight += 0.5;
            sim = new DrugStandardService().calculateTextSimilarity(String.valueOf(res1.get(0).getKey()), String.valueOf(res2.get(0).getKey()));
            similarityScore += sim * 0.5;
            totalWeight += 0.5;
        }

        System.out.println(similarityScore / totalWeight);
    }

    /**
     * 计算两个文本之间的相似度
     * 使用简单的编辑距离算法（Levenshtein Distance）
     *
     * @param text1 文本1
     * @param text2 文本2
     * @return 相似度分数（0-1之间，1表示完全相同）
     */
    private double calculateTextSimilarity(String text1, String text2) {

        if (text1 == null || text2 == null) {
            return 0.0;
        }

        // 转换为小写进行比较
        text1 = text1.toLowerCase().trim();
        text2 = text2.toLowerCase().trim();

        // 如果完全相等，直接返回1
        if (text1.equals(text2)) {
            return 1.0;
        }

        // 计算编辑距离
        int[][] dp = new int[text1.length() + 1][text2.length() + 1];

        // 初始化边界条件
        for (int i = 0; i <= text1.length(); i++) {
            dp[i][0] = i;
        }
        for (int j = 0; j <= text2.length(); j++) {
            dp[0][j] = j;
        }

        // 动态规划计算编辑距离
        for (int i = 1; i <= text1.length(); i++) {
            for (int j = 1; j <= text2.length(); j++) {
                if (text1.charAt(i - 1) == text2.charAt(j - 1)) {
                    dp[i][j] = dp[i - 1][j - 1];
                } else {
                    dp[i][j] = Math.min(Math.min(dp[i - 1][j], dp[i][j - 1]), dp[i - 1][j - 1]) + 1;
                }
            }
        }

        int editDistance = dp[text1.length()][text2.length()];
        int maxLength = Math.max(text1.length(), text2.length());

        // 如果最大长度为0，说明两个字符串都是空的
        if (maxLength == 0) {
            return 1.0;
        }

        // 计算相似度：1 - (编辑距离 / 最大长度)
        return 1.0 - (double) editDistance / maxLength;
    }



    /**
     * 新增药品信息
     *
     * @param request 请求参数
     * @return 上传结果
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public DrugInfoResponse uploadDrugInfo(DrugInfoRequest request) {
        DrugInfoResponse response = new DrugInfoResponse();
        response.setReqId(request.getReqId());
        logger.info("Starting to process drug info upload with reqId: {}", request.getReqId());
        try {
            // 3. 验证企业信息
            Entity entityInfo = queryEntityInfo("nocheck", request.getAppId());
            String entityId = entityInfo.getId();
            logger.info("Found entity with ID: {}", entityId);

            // 4. 处理数据
            List<DrugInfoResponse.DrugInfoData> responseDataList = new ArrayList<>();
            List<DrugInfoRequest.DrugInfoData> dataList = JSONUtil.parseArray(request.getData()).toList(DrugInfoRequest.DrugInfoData.class);
            for (int i = 0; i < dataList.size(); i++) {
                DrugInfoRequest.DrugInfoData item = dataList.get(i);
                // 使用统一验证方法
                ValidationResult validationResult = item.validate(i + 1);
                if (validationResult != null) {
                    response.setCode(validationResult.getCode());
                    response.setMsg(validationResult.getMessage());
                    return response;
                }
            }
            DrugUtils drugUtils = new DrugUtils();
            for (int i = 0; i < dataList.size(); i++) {
                DrugInfoRequest.DrugInfoData item = dataList.get(i);
                item.setYppzwh(item.getYppzwh().trim());//去取空格
                if(item.getGjybbm()==null) {
                    item.setGjybbm(item.getGjybbm().trim());
                }
                // 构建响应数据
                DrugInfoResponse.DrugInfoData responseData = new DrugInfoResponse.DrugInfoData();
                responseData.setId(item.getId());
                //判断localid是否已存在
                String localId = entityId+item.getId();
                String dbypwybsm = checkLocalIdExists(localId);
                if (dbypwybsm!=null) {
                    //直接返回唯一标识码
                    responseData.setYpwybsm(dbypwybsm);
                }else {

                    // 检查药品是否已存在
                    String ypwybsm = checkDrugExists(item,drugUtils);

                    // 如果不存在，则插入新记录
                    if (ypwybsm == null) {
                        ypwybsm = drugUtils.insertNewDrug(item, entityId, null,dmJdbcTemplate);
                    } else {
                        //也插入一条到
                        drugUtils.insertNewDrug(item, entityId, ypwybsm,dmJdbcTemplate);
                    }
                    responseData.setYpwybsm(ypwybsm);
                }
                responseDataList.add(responseData);
            }

            response.setCode(ResultCode.SUCCESS.getCode());
            response.setMsg(ResultCode.SUCCESS.getMessage());
            response.setData(responseDataList);

        } catch (Exception e) {
            logger.error("Error processing drug info upload with reqId: " + request.getReqId(), e);
            throw new RuntimeException(e); // 触发事务回滚
        }

        logger.info("Successfully processed drug info upload with reqId: {}", request.getReqId());
        return response;
    }

    private String checkLocalIdExists(String localId) {
        List<Map<String, Object>> result = dmJdbcTemplate.queryForList("SELECT YPWYBSM FROM YGYD_MAIN.DRUG_STANDARDS_REQ WHERE ID = ?",localId);
        if (!result.isEmpty()) {
            String ypwybsm = (String) result.get(0).get("YPWYBSM");
            logger.info("DRUG_STANDARDS_REQ already exists with localId: {}", localId);
            return ypwybsm;
        }
        return null;
    }

    // 工具方法：将Object转换为String
    private String getStringValue(Object obj) {
        return obj != null ? obj.toString() : null;
    }

    // 工具方法：将Object转换为Integer
    private Integer getIntegerValue(Object obj) {
        if (obj != null) {
            if (obj instanceof Integer) {
                return (Integer) obj;
            } else if (obj instanceof Number) {
                return ((Number) obj).intValue();
            } else {
                try {
                    return Integer.valueOf(obj.toString());
                } catch (NumberFormatException e) {
                    return null;
                }
            }
        }
        return null;
    }
}